package cc.messcat.util;

import cc.messcat.entity.system.ApplicationProperties;
import org.apache.commons.lang.StringUtils;
/**
 * 统计Sql
 * @author Administrator
 *
 */
public class SqlStatisUtil {
	
 
	/**
	 * 身体健康统计sql
	 * @param year
	 * @param poverty
	 * @param fatherId
	 * @param areaId
	 * @param sqlCondition
	 * @return
	 */
	public static StringBuffer HealthyStatisSql(String year, String poverty, Long fatherId, Long areaId, String sqlCondition) {
		StringBuffer totalCount = new StringBuffer();
		//sql条件：
		//变更类型
		String povertyStr = (StringUtils.isNotBlank(poverty) ? " AND pf.POVERTY = '" + poverty + "'": "");
		//业务年度
		String yearStr = (StringUtils.isNotBlank(year) ? " AND pf.ANNUAL = '" + year + "'": newYear());

		// 抽出相同sql部分
		StringBuffer sqlIdentical = new StringBuffer();
		sqlIdentical.append("SELECT COUNT(*)  FROM poor_family AS pf ,family AS fa,AREA AS a1 WHERE pf.id=fa.FID AND  pf.AREA_ID=a1.id  AND fa.IS_DEL='0' AND pf.IS_DEL='0' "+sqlCondition + povertyStr + yearStr );
		// 统计总sql
		totalCount.append("SELECT a.AREA '地区',	"
				// 贫困人口数
				+ "(" + sqlIdentical.toString() + ")  AS '贫困人口',"
				// 健康人数
				+ "(" + sqlIdentical.toString() + " AND fa.healthy = '健康') AS '健康',"
				// 长期慢性病人数
				+ "(" + sqlIdentical.toString() + " AND fa.healthy = '长期慢性病') AS '长期慢性病',"
				// 患有大病人数
				+ "(" + sqlIdentical.toString() + " AND fa.healthy = '患有大病') AS '患有大病',"
				// 残疾人数
				+ "(" + sqlIdentical.toString() + " AND fa.healthy = '残疾') AS '残疾'"

		+ " FROM AREA AS a WHERE   "+(areaId!=null ? "a.Id="+areaId : "a.fatherId=" + fatherId )+ "  GROUP BY a.Id ");
		return totalCount;
	}
	
	/**
	 * 贫困户文化程度人数统计sql
	 * @param year
	 * @param poverty
	 * @param fatherId
	 * @param areaId
	 * @param sqlCondition
	 * @return
	 */
	public static StringBuffer CultureStatisSql(String year, String poverty, Long fatherId, Long areaId, String sqlCondition) {
		StringBuffer totalCount = new StringBuffer();
		//sql条件：
		//变更类型
		String povertyStr = (StringUtils.isNotBlank(poverty) ? " AND pf.POVERTY = '" + poverty + "'": "");
		//业务年度
		String yearStr = (StringUtils.isNotBlank(year) ? " AND pf.ANNUAL = '" + year + "'": newYear());

		// 抽出相同sql部分
		StringBuffer sqlIdentical = new StringBuffer();
		sqlIdentical.append("SELECT COUNT(*)  FROM poor_family AS pf ,family AS fa,AREA AS a1 WHERE pf.id=fa.FID AND  pf.AREA_ID=a1.id  AND fa.IS_DEL='0' AND pf.IS_DEL='0' "+sqlCondition + povertyStr + yearStr );
		// 统计总sql
		totalCount.append("SELECT a.AREA '地区',	"
				// 贫困人口数
				+ "(" + sqlIdentical.toString() + ")  AS '贫困人口',"
				// 文盲或半文盲人数
				+ "(" + sqlIdentical.toString() + " AND fa.culture = '文盲或半文盲') AS '文盲或半文盲',"
				// 小学人数
				+ "(" + sqlIdentical.toString() + " AND fa.culture= '小学') AS '小学人数',"
				// 初中人数
				+ "(" + sqlIdentical.toString() + " AND fa.culture = '初中') AS '初中人数',"
				// 高中人数
				+ "(" + sqlIdentical.toString() + " AND fa.culture = '高中') AS '高中人数',"
				// 大专及以上人数
				+ "(" + sqlIdentical.toString() + " AND fa.culture = '大专及以上') AS '大专及以上',"
				// 学龄前儿童人数
				+ "(" + sqlIdentical.toString() + " AND fa.culture = '学龄前儿童') AS '学龄前儿童' "

		+ " FROM AREA AS a WHERE   "+(areaId!=null ? "a.Id="+areaId : "a.fatherId=" + fatherId )+ "  GROUP BY a.Id ");
		return totalCount;
	}
	/**
	 * 贫困户生产生活条件困难情况统计sql
	 * @param year
	 * @param poverty
	 * @param fatherId
	 * @param areaId
	 * @param sqlCondition
	 * @return
	 */
	public static StringBuffer DuctionLifeStatisSql(String year, String poverty, Long fatherId, Long areaId,
			String sqlCondition) {
		StringBuffer totalCount = new StringBuffer();
		//sql条件：
		//变更类型
		String povertyStr = (StringUtils.isNotBlank(poverty) ? " AND pf.POVERTY = '" + poverty + "'": "");
		//业务年度
		String yearStr = (StringUtils.isNotBlank(year) ? " AND pf.ANNUAL = '" + year + "'": newYear());

		// 抽出相同sql部分
		StringBuffer sqlIdentical = new StringBuffer();
		sqlIdentical.append(" FROM poor_family AS pf ,AREA AS a1 WHERE  pf.AREA_ID=a1.id   AND pf.IS_DEL='0'  "+sqlCondition + povertyStr + yearStr );
		// 统计总sql
		totalCount.append("SELECT a.AREA '地区',	"
				// 
				+ "(SELECT COUNT(*) " + sqlIdentical.toString() + " )  AS '贫困户数',"
				//
				+ "(SELECT COUNT(*) " + sqlIdentical.toString() + " AND pf.difficulty = '是' ) AS '饮水困难户数',"
				// 
				+ "(SELECT COUNT(*) " + sqlIdentical.toString() + " AND pf.security = '否' ) AS '无安全饮水户数',"
				// 
				+ "(SELECT COUNT(*) " + sqlIdentical.toString() + " AND pf.life = '否' ) AS '未通生活用电户数',"
				// 
				+ "(SELECT COUNT(*) " + sqlIdentical.toString() + " AND pf.life = '否') AS '未通广播电视户数(待定)',"
				// 
				+ "(SELECT COUNT(*) " + sqlIdentical.toString() + " AND pf.danger = '是') AS '住房是危房户数户数',"
				// 
				+ "(SELECT COUNT(*) " + sqlIdentical.toString() + " AND pf.toilet = '否') AS '无卫生厕所户数户数',"
				
				+ "(SELECT COALESCE(SUM(pf.housing),0)  " + sqlIdentical.toString() + " ) AS '人均住房面积',"
				
				+ "(SELECT COUNT(*)  FROM poor_family AS pf ,family AS fa,AREA AS a1 WHERE pf.id=fa.FID AND  pf.AREA_ID=a1.id  AND fa.IS_DEL='0' AND pf.IS_DEL='0'  "+sqlCondition + povertyStr + yearStr+")  AS '贫困人口数' "
				 

		+ " FROM AREA AS a WHERE   "+(areaId!=null ? "a.Id="+areaId : "a.fatherId=" + fatherId )+ "  GROUP BY a.Id ");
		return totalCount;
	}
	/**
	 * 	贫困户土地资源统计sql
	 * @param year
	 * @param poverty
	 * @param fatherId
	 * @param areaId
	 * @param sqlCondition
	 * @return
	 */
	public static StringBuffer LandResourceStatisSql(String year, String poverty, Long fatherId, Long areaId,
			String sqlCondition) {
		StringBuffer totalCount = new StringBuffer();
		//sql条件：
		//变更类型
		String povertyStr = (StringUtils.isNotBlank(poverty) ? " AND pf.POVERTY = '" + poverty + "'": "");
		//业务年度
		String yearStr = (StringUtils.isNotBlank(year) ? " AND pf.ANNUAL = '" + year + "'": newYear());
		// 抽出相同sql部分
		StringBuffer sqlIdentical = new StringBuffer();
		sqlIdentical.append(" FROM poor_family AS pf ,AREA AS a1 WHERE  pf.AREA_ID=a1.id   AND pf.IS_DEL='0'  "+sqlCondition + povertyStr + yearStr );
		// 统计总sql
		totalCount.append("SELECT a.AREA '地区',	"
				// 
				+ "(SELECT COUNT(*) " + sqlIdentical.toString() + " )  AS '贫困户数',"
				//
				+ "(SELECT COUNT(*)  FROM poor_family AS pf ,family AS fa,AREA AS a1 WHERE pf.id=fa.FID AND  pf.AREA_ID=a1.id  AND fa.IS_DEL='0' AND pf.IS_DEL='0'  "+sqlCondition + povertyStr + yearStr+")  AS '贫困人口数', "
				// 
				+ "(SELECT COALESCE(SUM(pf.CUED_LAND),0) " + sqlIdentical.toString() + " ) AS '耕地面积亩',"
				// 
				+ "(SELECT COALESCE(SUM(pf.IRRIGATION),0) " + sqlIdentical.toString() + " ) AS '有效灌溉面积亩',"
				// 
				+ "(SELECT COALESCE(SUM(pf.WOOD_LAND),0) " + sqlIdentical.toString() + " ) AS '林地面积亩',"
				// 
				+ "(SELECT COALESCE(SUM(pf.FARM_LAND),0) " + sqlIdentical.toString() + " ) AS '退耕还林面积亩',"
				// 
				+ "(SELECT COALESCE(SUM(pf.FRUIT),0) " + sqlIdentical.toString() + " ) AS '林果面积亩',"
				
				+ "(SELECT COALESCE(SUM(pf.FORA_GEGRASS),0) " + sqlIdentical.toString() + " ) AS '牧草地面积亩',"
				
				+ "(SELECT COALESCE(SUM(pf.WATERS),0) " + sqlIdentical.toString() + " ) AS '水域面积亩'"
				
		+ " FROM AREA AS a WHERE   "+(areaId!=null ? "a.Id="+areaId : "a.fatherId=" + fatherId )+ "  GROUP BY a.Id ");
		return totalCount;
	}
	/**
	 *  贫困户人均收入分组情况统计sql
	 * @param year
	 * @param poverty
	 * @param fatherId
	 * @param areaId
	 * @param sqlCondition
	 * @return
	 */
	public static  StringBuffer CapitaIncomeStatisSql(String year, String poverty, Long fatherId, Long areaId,
			String sqlCondition) {
		StringBuffer totalCount = new StringBuffer();
		//sql条件：
		//变更类型
		String povertyStr = (StringUtils.isNotBlank(poverty) ? " AND pf.POVERTY = '" + poverty + "'": "");
		//业务年度
		String yearStr = (StringUtils.isNotBlank(year) ? " AND pf.ANNUAL = '" + year + "'": newYear());
		// 抽出相同sql部分
		StringBuffer sqlIdentical = new StringBuffer();
		sqlIdentical.append(" FROM poor_family AS pf ,AREA AS a1 WHERE  pf.AREA_ID=a1.id   AND pf.IS_DEL='0'  "+sqlCondition + povertyStr + yearStr );
		// 统计总sql
		totalCount.append("SELECT a.AREA '地区',	"
				// 
				+ "(SELECT COUNT(*) " + sqlIdentical.toString() + " )  AS '贫困户数',"
			 
				+ "(SELECT COALESCE(SUM(pf.INCOME),0) " + sqlIdentical.toString() + " ) AS '总纯收入',"
				// 
				+ "(SELECT COUNT(*) " + sqlIdentical.toString() + " AND ifnull(pf.INCOME,0)>=0   AND ifnull(pf.INCOME,0)<500) AS '500元(不含)户数',"
				// 
				+ "(SELECT COUNT(*) " + sqlIdentical.toString() + " AND pf.INCOME>=500 AND pf.INCOME<1000) AS '500-1000元户数',"
				// 
				+ "(SELECT COUNT(*) " + sqlIdentical.toString() +" AND pf.INCOME>=1000 AND pf.INCOME<1500) AS '1000-1500元户数',"
				// 
				+ "(SELECT COUNT(*) " + sqlIdentical.toString() + " AND pf.INCOME>=1500 AND pf.INCOME<2000) AS '1500-2000元户数',"
				
				+ "(SELECT COUNT(*) " + sqlIdentical.toString() + " AND pf.INCOME>=2000 AND pf.INCOME<2952) AS '2000-2952元户数',"
				
				+ "(SELECT COUNT(*) " + sqlIdentical.toString() + " AND pf.INCOME>=2952 AND pf.INCOME<3100 ) AS '2952-3100元户数',"
						
				+ "(SELECT COUNT(*) " + sqlIdentical.toString() + " AND pf.INCOME>=3100  ) AS '3100元以上户数'"
						
				
		+ " FROM AREA AS a WHERE   "+(areaId!=null ? "a.Id="+areaId : "a.fatherId=" + fatherId )+ "  GROUP BY a.Id ");
		return totalCount;
	}
	/**
	 * 贫困户主要燃料分类统计表Sql
	 * @param year
	 * @param poverty
	 * @param fatherId
	 * @param areaId
	 * @param sqlCondition
	 * @return
	 */
	public static StringBuffer FuelTypeStatisSql(String year, String poverty, Long fatherId, Long areaId,
			String sqlCondition) {
		StringBuffer totalCount = new StringBuffer();
		//sql条件：
		//变更类型
		String povertyStr = (StringUtils.isNotBlank(poverty) ? " AND pf.POVERTY = '" + poverty + "'": "");
		//业务年度
		String yearStr = (StringUtils.isNotBlank(year) ? " AND pf.ANNUAL = '" + year + "'": newYear());
		// 抽出相同sql部分
		StringBuffer sqlIdentical = new StringBuffer();
		sqlIdentical.append(" SELECT COUNT(*)  FROM poor_family AS pf ,AREA AS a1 WHERE  pf.AREA_ID=a1.id   AND pf.IS_DEL='0'   "+sqlCondition + povertyStr + yearStr );
		// 统计总sql
		totalCount.append("SELECT a.AREA '地区',	"
				// 
				+ "(" + sqlIdentical.toString() + " )  AS '贫困户数',"
				// 
				+ "(" + sqlIdentical.toString() + " AND pf.FUEL='柴草') AS '柴草',"
				// 
//				+ "(" + sqlIdentical.toString() + " AND pf.FUEL='干蓄粪') AS '干蓄粪',"
				+ "(" + sqlIdentical.toString() + " AND pf.FUEL='干畜粪') AS '干畜粪',"
				// 
				+ "(" + sqlIdentical.toString() +" AND pf.FUEL='煤炭') AS '煤炭',"
				// 
				+ "(" + sqlIdentical.toString() + " AND pf.FUEL='清洁能源') AS '清洁能源',"
				
				+ "(" + sqlIdentical.toString() + " AND pf.FUEL='其他') AS '其他户数'"
						
		 + " FROM AREA AS a WHERE   "+(areaId!=null ? "a.Id="+areaId : "a.fatherId=" + fatherId )+ "  GROUP BY a.Id ");
		return totalCount;
	}
	
	/**
	 * 贫困户入户路情况分类统计Sql
	 * @param year
	 * @param poverty
	 * @param fatherId
	 * @param areaId
	 * @param sqlCondition
	 * @return
	 */
	public static StringBuffer EntranceRoadStatisSql(String year, String poverty, Long fatherId, Long areaId,
			String sqlCondition) {
		StringBuffer totalCount = new StringBuffer();
		//sql条件：
		//变更类型
		String povertyStr = (StringUtils.isNotBlank(poverty) ? " AND pf.POVERTY = '" + poverty + "'": "");
		//业务年度
		String yearStr = (StringUtils.isNotBlank(year) ? " AND pf.ANNUAL = '" + year + "'": newYear());
		// 抽出相同sql部分
		StringBuffer sqlIdentical = new StringBuffer();
		sqlIdentical.append(" FROM poor_family AS pf ,AREA AS a1 WHERE  pf.AREA_ID=a1.id   AND pf.IS_DEL='0'   "+sqlCondition + povertyStr + yearStr );
		// 统计总sql
		totalCount.append("SELECT a.AREA '地区',	"
				// 
				+ "(SELECT COUNT(*) " + sqlIdentical.toString() + " )  AS '贫困户数',"
				// 
				+ "(SELECT COALESCE(SUM(pf.distance),0) " + sqlIdentical.toString() + ") AS '距离主干路',"
				// 
				+ "(SELECT COUNT(*) " + sqlIdentical.toString() + " AND pf.type='泥土路') AS '普通泥土路',"
				// 
				+ "(SELECT COUNT(*) " + sqlIdentical.toString() +"  AND pf.type='砂石路') AS '砂石公路',"
				// 
				+ "(SELECT COUNT(*) " + sqlIdentical.toString() + " AND pf.type='水泥路') AS '水泥路面公路',"
				
				+ "(SELECT COUNT(*)" + sqlIdentical.toString() + "  AND pf.type='沥青路') AS '沥青路面公路'"
						
		 + " FROM AREA AS a WHERE   "+(areaId!=null ? "a.Id="+areaId : "a.fatherId=" + fatherId )+ "  GROUP BY a.Id ");
		return totalCount;
	}
	
	/**
	 * 贫困户家庭收入情况统计表Sql
	 * @param year
	 * @param poverty
	 * @param fatherId
	 * @param areaId
	 * @param sqlCondition
	 * @return
	 */
	public static StringBuffer FamilyIncomeStatisSql(String year, String poverty, Long fatherId, Long areaId,
			String sqlCondition) {
		StringBuffer totalCount = new StringBuffer();
		//sql条件：
		//变更类型
		String povertyStr = (StringUtils.isNotBlank(poverty) ? " AND pf.POVERTY = '" + poverty + "'": "");
		//业务年度
		String yearStr = (StringUtils.isNotBlank(year) ? " AND pf.ANNUAL = '" + year + "'": newYear());
		// 抽出相同sql部分
		StringBuffer sqlIdentical = new StringBuffer();
		sqlIdentical.append(" FROM poor_family AS pf ,AREA AS a1 WHERE  pf.AREA_ID=a1.id   AND pf.IS_DEL='0'   "+sqlCondition + povertyStr + yearStr );
		// 统计总sql
		totalCount.append("SELECT a.AREA '地区',	"
				//
//				+ "(SELECT COALESCE(SUM(pf.poor),0) FROM poor_village AS pf ,AREA AS a1 WHERE  pf.AREA_ID=a1.id   AND pf.IS_DEL='0' "+sqlCondition + yearStr + povertyStr + " )  AS '贫困户数',"
				+ "(select count(*) from poor_family AS pf ,AREA AS a1 WHERE  pf.AREA_ID=a1.id   AND pf.IS_DEL='0' "+sqlCondition + yearStr + povertyStr + " )  AS '贫困户数',"

//				+ "(SELECT COALESCE(SUM(pf.POOR_NUMBER),0) FROM poor_village AS pf ,AREA AS a1 WHERE  pf.AREA_ID=a1.id   AND pf.IS_DEL='0' "+sqlCondition + yearStr + povertyStr + " ) AS '贫困人口数',"
				+ "(select count(*) from poor_family as pf ,family as f ,AREA AS a1 WHERE  pf.AREA_ID=a1.id  AND pf.IS_DEL='0' and f.is_del='0' and f.annual=pf.annual and f.fid=pf.id  "+sqlCondition + yearStr + povertyStr + " ) AS '贫困人口数',"
				//
				+ "(SELECT COALESCE(SUM(pf.year),0)" + sqlIdentical.toString() + ") AS '年收入',"
				// 
				+ "(SELECT COALESCE(SUM(pf.operating),0)" + sqlIdentical.toString() +" ) AS '生产经营性收入',"
				// 
				+ "(SELECT COALESCE(SUM(pf.wages),0)" + sqlIdentical.toString() + ") AS '工资性收入',"
				
				+ "(SELECT COALESCE(SUM(pf.property),0)" + sqlIdentical.toString() + ") AS '财产性收入',"
				
				+ "(SELECT COALESCE(SUM(pf.transfer),0)" + sqlIdentical.toString() + ") AS '转移性收入',"
				
				+ "(SELECT COALESCE(SUM(pf.expenses),0)" + sqlIdentical.toString() + ") AS '生产经营性支出',"
				
				+ "(SELECT COALESCE(SUM(pf.income),0)" + sqlIdentical.toString() + ") AS '户均纯收入',"
						
				+ "(SELECT COALESCE(SUM(pf.perCapita),0)" + sqlIdentical.toString() + ") AS '人均纯收入'"
						
		 + " FROM AREA AS a WHERE   "+(areaId!=null ? "a.Id="+areaId : "a.fatherId=" + fatherId )+ "  GROUP BY a.Id ");
		return totalCount;
	}
	/**
	 * 贫困户转移性收入构成情况统计表Sql
	 * @param year
	 * @param poverty
	 * @param fatherId
	 * @param areaId
	 * @param sqlCondition
	 * @return
	 */
	public static StringBuffer TransferableIncStatisSql(String year, String poverty, Long fatherId, Long areaId,
			String sqlCondition) {
		StringBuffer totalCount = new StringBuffer();
		//sql条件：
		//变更类型
		String povertyStr = (StringUtils.isNotBlank(poverty) ? " AND pf.POVERTY = '" + poverty + "'": "");
		//业务年度
		String yearStr = (StringUtils.isNotBlank(year) ? " AND pf.ANNUAL = '" + year + "'": newYear());
		// 抽出相同sql部分
		StringBuffer sqlIdentical = new StringBuffer();
			sqlIdentical.append(" FROM poor_family AS pf ,AREA AS a1 WHERE  pf.AREA_ID=a1.id   AND pf.IS_DEL='0'  "+sqlCondition + povertyStr + yearStr );
			// 统计总sql
			totalCount.append("SELECT a.AREA '地区',	"
					// 
					+ "(SELECT COUNT(*)" + sqlIdentical.toString() + " )  AS '贫困户数',"
					// 
					+ "(SELECT COALESCE(SUM(pf.TRANSFER),0)" + sqlIdentical.toString() + ") AS '转移性收入',"
					// 
					+ "(SELECT COALESCE(SUM(pf.PLANNING),0)" + sqlIdentical.toString() + ") AS '划生育补贴金额',"
					// 
					+ "(SELECT COALESCE(SUM(pf.LOW),0)" + sqlIdentical.toString() +") AS '低保金金额',"
					// 
					+ "(SELECT COALESCE(SUM(pf.PENSION),0)" + sqlIdentical.toString() + ") AS '领取养老金',"
					
					+ "(SELECT COALESCE(SUM(pf.ECOLOGY),0)" + sqlIdentical.toString() + ") AS '生态补偿金'"
						
		 + " FROM AREA AS a WHERE   "+(areaId!=null ? "a.Id="+areaId : "a.fatherId=" + fatherId )+ "  GROUP BY a.Id ");
		return totalCount;
	}
 
	
 
	/**
	 * 获取当前年份
	 * 
	 * @return
	 */
	public static String newYear() {
		return " AND pf.ANNUAL = '"+ApplicationProperties.getAnnual()+"'";
	}
	
}
